package com.ai.bitcoin.jpa.dao;

import com.ai.bitcoin.jpa.entity.BccSubCoin;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface BccSubCoinDao extends JpaRepository<BccSubCoin, String> {

    @Query(value = "select * from bcc_sub_coin where status = 1 and user_open_id = ?1 and end_time > UNIX_TIMESTAMP() order by update_time DESC", nativeQuery = true)
    List<BccSubCoin> queryValidByUserSymbol(String openId);

    @Query(value = "SELECT sub.* " +
            "FROM bcc_sub_coin sub " +
            "WHERE sub.status = 1 " +
            "  AND sub.end_time > unix_timestamp() " +
            "  AND exists(SELECT 1 " +
            "             FROM bcc_report_monitor_config config " +
            "             WHERE config.bcc_id = sub.bcc_id " +
            "               AND config.open_id = sub.user_open_id " +
            "               AND config.report_subscribe_switch = 1)", nativeQuery = true)
    List<BccSubCoin> queryValidRecord();

    @Query(nativeQuery = true, value = "update bcc_sub_coin set status = 0 ,update_time = NOW() where end_time < UNIX_TIMESTAMP()")
    @Modifying
    public int checkExpireSubCoin();

    @Query(value = "select * from bcc_sub_coin where bcc_id = ?1 AND user_open_id = ?2  for UPDATE ", nativeQuery = true)
    BccSubCoin queryByBccIdAndUserOpenId(String bccId, String userOpenId);

    @Query(nativeQuery = true, value = "update bcc_sub_coin set status = 1 ,update_time = NOW(),end_time = ?1 where bcc_id = ?2 and user_symbol = ?3")
    @Modifying
    int updateEndTimeByBccIdAndUserSymbol(Long endTime, String bccId, String userSymbol);

    BccSubCoin findByUserOpenIdAndBccIdAndStatus(String openId, String bccId, Integer status);

    BccSubCoin findByUserOpenIdAndBccId(String openId, String bccId);

    @Query(value = "select * from bcc_sub_coin where status = 1 and bcc_id = ?1 and end_time > UNIX_TIMESTAMP() order by update_time DESC", nativeQuery = true)
    List<BccSubCoin> queryValidByBccId(String bccId);


    @Query(nativeQuery = true, value = "SELECT bcc_id FROM bcc_sub_coin WHERE  user_open_id = ?1 and status = 1 GROUP BY bcc_id")
    @Modifying
    List<String> queryBccIdsByOpenId(String openId);

    @Query(nativeQuery = true, value = "SELECT bcc_id FROM bcc_sub_coin WHERE  status in (?1) and user_open_id = ?2  GROUP BY bcc_id")
    @Modifying
    List<String> queryBccIdsByStatus(String status, String openId);

    @Query(nativeQuery = true, value = "update bcc_sub_coin set status = 1,end_time = ?2  where id = ?1")
    @Modifying
    @Transactional
    int updateById(String id, Long endTime);

    @Query(nativeQuery = true, value = "delete from bcc_sub_coin where bcc_id = ?1 and user_open_id = ?2 and status = ?3")
    @Modifying
    @Transactional
    void deleteSubCoinById(String bccId, String openId, int status);

    List<BccSubCoin> findByBccIdAndStatus(String bccId, Integer status);

    void deleteByUserOpenId(String openId);
}
